-- @owner: zou_jialiang050
-- @date: 2024/4/2
-- @testpoint: pipelined函数返回嵌套表类型，其中嵌套复合类型

--step1:创建嵌套表类型，其中嵌套复合类型;expect:成功
create or replace type associative_array_0008 as(key text, value text);
create or replace type type_0008 as (c1 associative_array_0008);
create or replace type tb_type_0008 as table of type_0008;

--step2:创建管道函数并查看返回值;expect:成功
create or replace function func_pipelined_0008(count in number)
returns tb_type_0008 pipelined
as $$
declare
temp_assoc associative_array_0008;
temp_type type_0008;
begin
for i in 1 .. count loop
temp_assoc.key := 'key' || i::TEXT;
temp_assoc.value := 'value' || i::TEXT;
temp_type.c1 := temp_assoc;
PIPE ROW(temp_type);
temp_assoc.key := '';
temp_assoc.value := '';
temp_type.c1 := temp_assoc;
PIPE ROW(temp_type);
temp_assoc.key := null;
temp_assoc.value := null;
PIPE ROW(temp_type);
end loop;
return;
end;
$$ language plpgsql;
/

--step3:调用pipelined函数并查看返回类型;expect:成功
select func_pipelined_0008(3);
select * from func_pipelined_0008(3);
select * from table(func_pipelined_0008(3));
select c1 from table(func_pipelined_0008(3));
select pg_typeof(func_pipelined_0008(3));

--step4:使用alter修改函数名称;expect:成功
alter function func_pipelined_0008(count in number) rename to func_pipelined_0008_1;
select func_pipelined_0008_1(3);
select * from func_pipelined_0008_1(3);
select * from table(func_pipelined_0008_1(3));

--step5:使用create table as创建表;expect:成功
drop table if exists t_pipelined_0008_2;
create table t_pipelined_0008_2 as select * from func_pipelined_0008_1(1);
insert into t_pipelined_0008_2 (select * from table(func_pipelined_0008_1(1)));
select * from t_pipelined_0008_2;

--step6:清理环境;expect:成功
drop function if exists func_pipelined_0008_1;
drop type if exists tb_type_0008 cascade;
drop type if exists type_0008 cascade;
drop type if exists associative_array_0008 cascade;
drop table if exists t_pipelined_0008_2;
